package com.cheng.example.excel.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ConverterKeyBuild;
import com.alibaba.excel.converters.DefaultConverterLoader;
import com.cheng.example.excel.easyexcel.bean.PayDemo;
import com.cheng.example.excel.easyexcel.converter.StringNumberConverter;
import com.cheng.example.excel.easyexcel.listener.NoModleDataListener;
import com.cheng.example.excel.easyexcel.listener.PayDemoListener;
import com.cheng.example.excel.easyexcel.util.RunUtil;
import com.cheng.example.excel.easyexcel.util.TestFileUtil;
import com.cheng.example.excel.easyexcel.util.util.ExcelUtil;
import com.cheng.example.util.VmUtil;
import org.junit.Test;

import java.io.File;
import java.util.*;


public class EasyExcelTest {


    static {
        StringNumberConverter converter =new StringNumberConverter();
        Map<String, Converter> allConverter=DefaultConverterLoader.loadAllConverter();
        allConverter.put(ConverterKeyBuild.buildKey(converter.supportJavaTypeKey(), converter.supportExcelTypeKey()), converter);
    }

    /**
     * 导出excle
     * 单个Sheet导出
     * 无模型映射导出
     */
    @Test
    public void excelExport01() {
        String filePath = TestFileUtil.getPath() + "demo" + File.separator + "exportj.xlsx";
        System.out.println(filePath);
        List<List<Object>> data = new ArrayList<>();
        data.add(Arrays.asList("111", "222", "333"));
        data.add(Arrays.asList("111", "222", "333"));
        data.add(Arrays.asList("111", "222", "333"));
        List<String> head = Arrays.asList("表头1", "表头2", "表头3");
        ExcelUtil.writeBySimple(filePath, data, head);
    }

    /**
     * 导入excel
     */
    @Test
    public void excelImport01() {
//        String filePath = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        VmUtil.printMemory();
        File file = new File("E:\\RATE_ERP_CASHVALUE72003.xlsx");
        String filePath = file.getPath();
        long start = System.currentTimeMillis();
//        long end = System.currentTimeMillis();
//        System.out.println(end - start);
//        List<Object> objects = ExcelUtil.readLessThan1000Row(filePath);
        List<Object> objects = ExcelUtil.readMoreThan1000Row(filePath);

        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
//        List<Map<String, String>> listMap = EasyExcel.read(filePath).sheet().doReadSync();
//        System.out.println("集合个数\t"+listMap.size());
//        System.out.println(listMap);

//        List<Object> cloneOb = new ArrayList<>();
//        BeanUtils.copyProperties(objects, cloneOb);
//        VmUtil.printMemory();
        long end = System.currentTimeMillis();
        System.out.println("执行时间:\t" + ((end - start) / 1000));
        VmUtil.printMemory();
        System.out.println("集合个数\t" + objects.size());
//        String filePath = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
//        //第一个1代表sheet1, 第二个1代表从第几行开始读取数据，行号最小值为0
//        Sheet sheet = new Sheet(1, 1);
//        List<Object> objects = ExcelUtil.readLessThan1000RowBySheet(filePath,sheet);
//        System.out.println(objects);
//        System.out.println(StringUtils.hasText("\n  \t"));
    }

    /**
     * 一次性全部读取到内存中
     * 100万条
     */
    @Test
    public void excelImport02() {
        VmUtil.printMemory();
        File file = new File("E:\\RATE_ERP_CASHVALUE72003.xlsx");
        String filePath = file.getPath();
        long start = System.currentTimeMillis();
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
        List<Map<String, String>> listMap = EasyExcel.read(filePath).sheet().doReadSync();
        System.out.println("集合个数\t" + listMap.size());
        System.out.println(listMap);
        long end = System.currentTimeMillis();
        System.out.println("执行时间:\t" + ((end - start) / 1000));
        System.out.println(listMap.size());
    }

    /**
     * 分段获得excel
     * 10万条
     */
    @Test
    public void excelImport03() {
        VmUtil.printMemory();
        File file = new File("E:\\RATE_ERP_CASHVALUE72003.xlsx");
        String filePath = file.getPath();
        long start = System.currentTimeMillis();
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
        List<Map<String, String>> listMap = EasyExcel.read(filePath).sheet().headRowNumber(900 * 1000).doReadSync();
        VmUtil.printMemory();
        System.out.println("集合个数\t" + listMap.size());
        System.out.println(listMap);
        long end = System.currentTimeMillis();
        System.out.println("执行时间:\t" + ((end - start) / 1000));
        System.out.println(listMap.size());
    }

    /**
     * 分段获得excel
     * 1万条
     */
    @Test
    public void excelImport04() {
        VmUtil.printMemory();
        File file = new File("E:\\RATE_ERP_CASHVALUE72003.xlsx");
        String filePath = file.getPath();
        long start = System.currentTimeMillis();
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
        List<Map<String, String>> listMap = EasyExcel.read(filePath).sheet().headRowNumber(1036312).doReadSync();
        VmUtil.printMemory();
        System.out.println("集合个数:\t" + listMap.size());
        System.out.println(listMap);
        long end = System.currentTimeMillis();
        System.out.println("执行时间:\t" + ((end - start) / 1000));
        System.out.println(listMap.size());
    }

    /**
     * 异步41万
     */
//    @Test
//    public void excelImport05(){
//        VmUtil.printMemory();
//        File file = new File("D:\\YSFJwork\\任务\\002-excel性能优化\\RATE_ERP_CASHVALUE7200.xlsx");
//        String filePath = file.getPath();
//        long start = System.currentTimeMillis();
//        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
//        PayDemoListener payDemoListener=new PayDemoListener();
//        EasyExcel.read(filePath, LinkedHashMap.class, payDemoListener).sheet().headRowNumber(2).doRead();
//        System.out.println(payDemoListener.getList().size());
//        List<LinkedHashMap<Integer, String>> listMap = payDemoListener.getList();
//        System.out.println(listMap.size());
//        VmUtil.printMemory();
//        long end = System.currentTimeMillis();
//        System.out.println("执行时间:\t"+((end - start)/1000)+"秒");
//    }


    /**
     * 同步41万条
     */
    @Test
    public void excelImport06() {
        VmUtil.printMemory();
        File file = new File("D:\\YSFJwork\\任务\\002-excel性能优化\\7000-01-41w.xlsx");
        String filePath = file.getPath();
        long start = System.currentTimeMillis();
        List<Map<String, String>> listMap = EasyExcel.read(filePath).sheet().headRowNumber(1).doReadSync();
        System.out.println(listMap);
        VmUtil.printMemory();
        long end = System.currentTimeMillis();
        System.out.println("执行时间:\t" + ((end - start) / 1000) + "秒");
    }


    /**
     * 异步2条
     */
    @Test
    public void excelImport07() {
        VmUtil.printMemory();
        File file = new File("D:\\YSFJwork\\任务\\002-excel性能优化\\7000-01-2t.xlsx");
        String filePath = file.getPath();
        long start = System.currentTimeMillis();
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
        PayDemoListener payDemoListener = new PayDemoListener();
        //打印maplist
//        EasyExcel.read(filePath, PayDemo.class, payDemoListener).sheet().headRowNumber(2).doRead();
//        System.out.println(payDemoListener.getList().size());
//        List<Map<Object, Object>> listMap = payDemoListener.getMapArrayList();
//        System.out.println(listMap.size());
//        System.out.println(listMap);


        //打印对象集合
        List<Map<String, String>> mapList=EasyExcel.read(filePath, PayDemo.class, payDemoListener).sheet().headRowNumber(2).doReadSync();

        VmUtil.printMemory();
        long end = System.currentTimeMillis();
        System.out.println("执行时间:\t" + ((end - start) / 1000) + "秒");
    }


    /**
     * 无模式导入
     */
    @Test
    public void excelImport08() {


        VmUtil.printMemory();
        File file = new File("D:\\YSFJwork\\任务\\002-excel性能优化\\7000-01-41w.xlsx");
        String filePath = file.getPath();
        long start = System.currentTimeMillis();
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
        NoModleDataListener noModleDataListener = new NoModleDataListener();
        EasyExcel.read(filePath, noModleDataListener).sheet().headRowNumber(2).doRead();
        System.out.println(noModleDataListener.getList().size());
        VmUtil.printMemory();
        long end = System.currentTimeMillis();
        System.out.println("执行时间:\t" + ((end - start) / 1000) + "秒");
    }



    /**
     * 测试对象大小
     */
    @Test
    public void excelImport09() {
        List<Map<Long, String>> mapList = new ArrayList<>();
        RunUtil runUtil= RunUtil.getInstance().start();
        VmUtil.printMemory();
        for (int i = 0; i < 40*10000; i++) {
            Map<Long, String> map = new LinkedHashMap();
            map.put(System.nanoTime(), System.currentTimeMillis()+"");
            map.put(System.nanoTime(), System.currentTimeMillis()+"");
            map.put(System.nanoTime(), System.currentTimeMillis()+"");
            map.put(System.nanoTime(), System.currentTimeMillis()+"");
            map.put(System.nanoTime(), System.currentTimeMillis()+"");
            map.put(System.nanoTime(), System.currentTimeMillis()+"");
            map.put(System.nanoTime(), System.currentTimeMillis()+"");
            map.put(System.nanoTime(), System.currentTimeMillis()+"");
            mapList.add(map);
        }
        VmUtil.printMemory();
        runUtil.end();
    }




    /**
     * 同步2条
     */
    @Test
    public void excelImport10() {
        VmUtil.printMemory();
        File file = new File("D:\\YSFJwork\\任务\\002-excel性能优化\\dat\\04.xls");
        String filePath = file.getPath();
        long start = System.currentTimeMillis();

        //打印对象集合
        List<Map<Integer, String>> mapList=EasyExcel.read(filePath).sheet().headRowNumber(2).doReadSync();

        for (Map map : mapList) {
            System.out.println(map);
        }
        VmUtil.printMemory();
        long end = System.currentTimeMillis();
        System.out.println("执行时间:\t" + ((end - start) / 1000) + "秒");
    }



}
